package com.ridbor001.demo.dao;

import com.ridbor001.demo.bean.RDBStudent;
import com.ridbor001.demo.util.RDBDataStore;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

public class RDBStudentDAO {
    RDBDataStore dataStore = new RDBDataStore();
    // 批量获取学生
    public Map<String, RDBStudent> getStudentsByIds(Set<String> studentIds) throws SQLException {
        if (studentIds == null || studentIds.isEmpty()) {
            return Collections.emptyMap();
        }

        String placeholders = studentIds.stream()
                .map(id -> "?")
                .collect(Collectors.joining(","));

        String sql = "SELECT * FROM students WHERE student_id IN (" + placeholders + ")";
        Map<String, RDBStudent> studentMap = new HashMap<>();

        try (Connection conn =  dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int index = 1;
            for (String studentId : studentIds) {
                pstmt.setString(index++, studentId);
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    RDBStudent student = new RDBStudent(
                            rs.getString("name"),
                            rs.getString("password"),
                            rs.getString("gender"),
                            rs.getInt("age"),
                            rs.getString("phone"),
                            rs.getString("student_id"),
                            rs.getString("class_name"),
                            rs.getString("major")
                    );
                    studentMap.put(student.getStudentId(), student);
                }
            }
        }
        return studentMap;
    }

    // 获取所有学生
    public  List<RDBStudent> getAllStudents() throws SQLException {
        String sql = "SELECT * FROM students";
        List<RDBStudent> students = new ArrayList<>();

        try (Connection conn = dataStore.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                students.add(new RDBStudent(

                        rs.getString("name"),
                        rs.getString("password"),
                        rs.getString("gender"),
                        rs.getInt("age"),
                        rs.getString("phone"),
                        rs.getString("student_id"),
                        rs.getString("class_name"),
                        rs.getString("major")
                ));
            }
        }
        return students;
    }
    // 搜索学生
    public List<RDBStudent> searchStudents(String keyword) throws SQLException {
        String sql = "SELECT * FROM students "
                + "WHERE student_id LIKE ? OR name LIKE ? OR class_name LIKE ? OR major LIKE ?";

        List<RDBStudent> students = new ArrayList<>();
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            String likeKeyword = "%" + keyword + "%";
            pstmt.setString(1, likeKeyword);
            pstmt.setString(2, likeKeyword);
            pstmt.setString(3, likeKeyword);
            pstmt.setString(4, likeKeyword);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    students.add(new RDBStudent(
                            rs.getString("name"),
                            rs.getString("password"),
                            rs.getString("gender"),
                            rs.getInt("age"),
                            rs.getString("phone"),
                            rs.getString("student_id"),
                            rs.getString("class_name"),
                            rs.getString("major")
                    ));
                }
            }
        }
        return students;
    }
    // 根据课程获取学生列表
    public   List<RDBStudent> getStudentsByCourse(String courseId) throws SQLException {
        String sql = "SELECT s.* "
                + "FROM students s "
                + "JOIN student_courses sc ON s.student_id = sc.student_id "
                + "WHERE sc.course_id = ?";

        List<RDBStudent> students = new ArrayList<>();
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, courseId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    students.add(new RDBStudent(
                            rs.getString("name"),
                            rs.getString("password"),
                            rs.getString("gender"),
                            rs.getInt("age"),
                            rs.getString("phone"),
                            rs.getString("student_id"),
                            rs.getString("class_name"),
                            rs.getString("major")
                    ));
                }
            }
        }
        return students;
    }

    public   boolean studentExists(String studentId) throws SQLException {
        final String sql = "SELECT COUNT(*) FROM students WHERE student_id = ?";
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }
    // 根据学生ID和密码查找学生
    public  Optional<RDBStudent> findStudentByIdAndPassword(String studentId, String password)
            throws SQLException {

        // 使用参数化查询防止SQL注入
        String sql = "SELECT * FROM students WHERE student_id = ? AND password = ?";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置参数
            pstmt.setString(1, studentId);
            pstmt.setString(2, password);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    // 从结果集构建学生对象
                    return Optional.of(new RDBStudent(
                            rs.getString("name"),
                            rs.getString("password"),
                            rs.getString("gender"),
                            rs.getInt("age"),
                            rs.getString("contact"),
                            rs.getString("student_id"),
                            rs.getString("class_name"),
                            rs.getString("major")
                    ));
                }
            }
        }
        return Optional.empty();
    }

    public   RDBStudent findStudentById(String studentId) throws SQLException {
        final String sql = "SELECT student_id, name, password, gender, age, "
                + "contact, class_name, major "
                + "FROM students WHERE student_id = ?";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new RDBStudent(
                            rs.getString("name"),
                            rs.getString("password"),
                            rs.getString("gender"),
                            rs.getInt("age"),
                            rs.getString("contact"),
                            rs.getString("student_id"),
                            rs.getString("class_name"),
                            rs.getString("major")
                    );
                }
            }
        }
        return null;
    }



    // 添加学生
    public  void addStudent(RDBStudent student) throws SQLException {
        String sql = "INSERT INTO students (" +
                "student_id, name, password, gender, age, contact, class_name, major" +
                ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; // 8个占位符

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, student.getStudentId());
            pstmt.setString(2, student.getName());
            pstmt.setString(3, student.getPassword());
            pstmt.setString(4, student.getGender());
            pstmt.setInt(5, student.getAge());
            pstmt.setString(6, student.getPhone());    // 固定：联系方式
            pstmt.setString(7, student.getClassName());   // 固定：班级
            pstmt.setString(8, student.getMajor());       // 固定：专业

            pstmt.executeUpdate();
        }
    }
}
